Imports System
Imports System.Data
Imports Npgsql

Public Class BaseDeDatos

    Private conn As NpgsqlConnection

    Private stringConnection As String = "Server=127.0.0.1;Port=5432;User Id=postgres;Password=golden;Database=bdmsh;"


    Public Sub New()
        Try
            conn = New NpgsqlConnection(stringConnection)
        Catch ex As NpgsqlException
            MsgBox(ex)
        End Try
    End Sub

    Private Sub Open()
        conn.Open()
    End Sub

    Private Sub Close()
        conn.Close()
    End Sub

    Public Sub EjecutarSinRetorno(ByVal query As String)
        Try
            Me.Open()
            Dim command As New NpgsqlCommand(query, conn)
            command.ExecuteNonQuery()
            Me.Close()
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try

    End Sub

    Public Function EjecutarConRetorno(ByVal query As String) As Hashtable
        Dim array As New Hashtable
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()

            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()

            Dim i As Integer
            While dr.Read()
                For i = 0 To dr.FieldCount - 1
                    Dim dato As String = CStr(dr(dr.GetName(i).ToString))
                    Dim nombrecampo As String = dr.GetName(i).ToString
                    array.Add(nombrecampo, dato)
                Next i
            End While

            Me.Close()

            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing

    End Function

    Public Function obtenerAsistencia(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Asistencia
            While dr.Read()
                Objeto = New Asistencia(CStr(dr("fecha")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing

    End Function

    Public Function obtenerBodega(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Bodega
            While dr.Read()
                Objeto = New Bodega(CStr(dr("tipo")), Nothing)
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing

    End Function

    Public Function obtenerGuiaDespacho(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As GuiaDespacho
            While dr.Read()
                Objeto = New GuiaDespacho(CStr(dr("giro")), _
                CStr(dr("ciudad")), _
                CStr(dr("comuna")), _
                CStr(dr("id")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerItemFactura(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Item
            While dr.Read()
                Objeto = New Item(CStr(dr("coditem")), _
                CStr(dr("descripcion")), _
                CStr(dr("cantidad")), _
                CStr(dr("preciounitario")), _
                CStr(dr("nombre")), _
                CStr(dr("iva")), 0)
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obteneritemOrdenCompra(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As ItemDocumentos
            While dr.Read()
                Objeto = New ItemDocumentos(CStr(dr("id")), CStr(dr("nombre")), "", _
                CStr(dr("descripcion")), CStr(dr("cantidad")), CStr(dr("preciounitario")), CStr(dr("coditem")), _
                "")
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerCliente(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Cliente
            While dr.Read()
                Objeto = New Cliente(CStr(dr("rut")), CStr(dr("rznsocial")), _
                CStr(dr("direccion")), _
                CStr(dr("giro")), _
                CStr(dr("telefono")), _
                CStr(dr("comuna")), _
                CStr(dr("region")), _
                CStr(dr("contacto")), _
                CStr(dr("ciudad")), _
                CStr(dr("fax")), _
                CStr(dr("email")), _
                CStr(dr("id")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerGrupo(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As GrupoUsuario
            While dr.Read()
                Objeto = New GrupoUsuario(CStr(dr("id")), CStr(dr("nombregrupo")), CStr(dr("accesoadministracion")), CStr(dr("accesoinformes")), CStr(dr("accesovistapanol")), CStr(dr("accesobodega")), _
                                        CStr(dr("accesoindicadores")), CStr(dr("accesobusqueda")), CStr(dr("accesorecursoshumanos")), CStr(dr("accesoplanificacion")), CStr(dr("accesocontabilidad")), _
                                        CStr(dr("accesosistema")), CStr(dr("accesosistemageneral")), CStr(dr("accesosolicitudbodega")), CStr(dr("accesosistemapersonal")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing

    End Function

    Public Function obtenerEmpleado(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Empleado
            While dr.Read()
                Objeto = New Empleado(CStr(dr("nombre")), CStr(dr("apellido")), _
                CStr(dr("rut")), _
                CStr(dr("direccion")), _
                CStr(dr("telefono")), _
                CStr(dr("profesion")), _
                CStr(dr("edad")), _
                CStr(dr("fechainicioempresa")), _
                CStr(dr("fechafinempresa")), _
                CStr(dr("sexo")), _
                CStr(dr("fechanacimiento")), _
                CStr(dr("nacionalidad")), _
                CStr(dr("paisnacimiento")), _
                CStr(dr("regionnacimiento")), _
                CStr(dr("comunanacimiento")), _
                CStr(dr("ciudadnacimiento")), _
                CStr(dr("provinciaresidencia")), _
                CStr(dr("ciudadresidencia")), _
                CStr(dr("celular")), _
                CStr(dr("email")), _
                CStr(dr("estadocivil")), _
                CStr(dr("cargasfamiliares")), _
                CStr(dr("numerocalzado")), _
                CStr(dr("talla")), CStr(dr("rutafoto")))
                array.Add(Objeto)

            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerOrdenTrabajo(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As OrdenTrabajo
            While dr.Read()
                Objeto = New OrdenTrabajo(CStr(dr("descripcion")), CInt(dr("id")), CStr(dr("fkempleado")), CInt(dr("fkpresupuesto")), CStr(dr("fecha")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerRequisicionItem(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As ItemDocumentos
            While dr.Read()
                Objeto = New ItemDocumentos(CStr(dr("id")), CStr(dr("item")), "", _
                CStr(dr("detalle")), CStr(dr("cantidad")), CStr(dr("preciounitario")), CStr(dr("fkmaterial")), _
                CStr(dr("fkrequisicion")))
                Objeto.FKProveedor = CStr(dr("fkproveedor"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerRequisicion(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim rutProv As String = ""
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Requisicion
            While dr.Read()
                Objeto = New Requisicion(CStr(dr("id")), CStr(dr("prioridad")), _
                CStr(dr("unidad")), CStr(dr("preciounitario")), CStr(dr("cantidad")), _
                CStr(dr("um")), CStr(dr("descripcion")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing

    End Function

    Public Function obtenerSubProyectoCriterio(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim rutProv As String = ""
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As presupuestosubproyecto
            While dr.Read()
                Objeto = New presupuestosubproyecto(CStr(dr("id")), CStr(dr("nombresubproyecto")), CStr(dr("descripcionsubproyecto")), CStr(dr("observaciontaller")), CStr(dr("notasalcliente")), _
                                                    CStr(dr("notasventas")), CStr(dr("fkpresupuesto")), CStr(dr("itemaprovado")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerPresupuesto(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Presupuesto
            While dr.Read()

                Objeto = New Presupuesto(CStr(dr("id")), CStr(dr("fecha")), CStr(dr("condventa")), CStr(dr("atencion")), CStr(dr("garantias")), CStr(dr("entrega")), CStr(dr("plano")), _
                                         CStr(dr("neto")), CStr(dr("iva")), CStr(dr("total")), CStr(dr("gastosgeneralesporcentaje")), CStr(dr("utilidadporcentajes")), CStr(dr("utilidadmaterialesporcentaje")), CStr(dr("descuentoporcentaje")), _
                                         CStr(dr("fkcliente")), CStr(dr("fkvendedor")), CStr(dr("requisicion")), CStr(dr("estado")), CStr(dr("estimadosenor")), CStr(dr("precios")), CStr(dr("condiciones")), CStr(dr("plazoentrega")), CStr(dr("lugarentrega")), CStr(dr("validezoferta")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerProveedor(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Proveedor
            While dr.Read()
                Objeto = New Proveedor(CStr(dr("rut")), CStr(dr("rznsocial")), _
                CStr(dr("direccion")), CStr(dr("giro")), CStr(dr("telefono")), CStr(dr("comuna")), _
                CStr(dr("region")), CStr(dr("contacto")), CStr(dr("ciudad")), CStr(dr("fax")), CStr(dr("email")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerMaterialProveedor(ByVal query As String) As ArrayList
        Dim arrayIdMaterial As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As String
            While dr.Read()
                Objeto = CStr(dr("fkmaterial"))
                arrayIdMaterial.Add(Objeto)
            End While
            Me.Close()
            Return obtenerMaterialProveedor(arrayIdMaterial)
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerMaterialProveedor(ByVal arrayIDMat As ArrayList) As ArrayList
        Dim query As String = ""
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader

        Try
            For i As Integer = 0 To arrayIDMat.Count - 1
                query = "select * from material where id='" + arrayIDMat.Item(i).ToString = "'"
                Me.Open()
                Dim mydataset As New DataSet
                Dim command As New NpgsqlCommand(query, conn)
                dr = command.ExecuteReader()
                Dim Objeto As Familia
                While dr.Read()
                    Objeto = New Familia()
                    Objeto.Id = CStr(dr("id"))
                    Objeto.Nombre = CStr(dr("nombre"))
                    Objeto.Codigo = CStr(dr("codigo"))
                    Objeto.Descripcion = CStr(dr("descripcion"))
                    Objeto.Bodega = CStr(dr("bodega"))
                    array.Add(Objeto)
                End While
            Next
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerMaterial(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Material
            Objeto = New Material
            While dr.Read()

                Objeto.Nombre = CStr(dr("nombre"))
                Objeto.Codigo = CStr(dr("codmaterial"))
                Objeto.ID = CStr(dr("id"))
                Objeto.DescripcionFormal = CStr(dr("descripcionformal"))
                Objeto.DescripcioninFormal = CStr(dr("descripcioninformal"))
                Objeto.STOCKMINIMO = CStr(dr("stockminimo"))
                Objeto.RefFAmilia = CStr(dr("reffamilia"))
                array.Add(Objeto)
            End While

            If Objeto.ID.Length > 0 Then

                query = "select * from materialdatos where fkmaterial=" + Objeto.ID
                Dim command2 As New NpgsqlCommand(query, conn)
                dr = command2.ExecuteReader()
                Dim Objeto2 As MaterialDatos
                Objeto2 = New MaterialDatos
                While dr.Read()

                    Objeto2.Id = CStr(dr("id"))
                    Objeto2.Cantidad = CStr(dr("cantidad"))
                    Objeto2.FechaIngreso = CStr(dr("fechaingreso"))
                    Objeto2.UbicacionFila = CStr(dr("ubicacionfila"))
                    Objeto2.UbicacionColumna = CStr(dr("ubicacioncolumna"))
                    Objeto2.FkMaterial = CStr(dr("fkmaterial"))
                    Objeto2.FkBodega = CStr(dr("fkbodega"))
                    Objeto2.FkOrdenCompra = CStr(dr("fkordencompra"))
                End While

                Objeto.RefMaterialDatos = Objeto2
            End If
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerFamilia(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Familia
            While dr.Read()
                Objeto = New Familia()
                Objeto.Id = CStr(dr("id"))
                Objeto.Nombre = CStr(dr("nombre"))
                Objeto.Codigo = CStr(dr("codigo"))
                Objeto.Descripcion = CStr(dr("descripcion"))
                Objeto.Bodega = CStr(dr("bodega"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerMaterialProv(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As MaterialPendiente
            While dr.Read()
                Objeto = New MaterialPendiente()

                Objeto.IdMaterialPendiente = CInt(CStr(dr("id")))
                Objeto.Nombre = CStr(dr("nombre"))
                Objeto.Estado = CStr(dr("estado"))
                Objeto.Codigo = CStr(dr("codmaterial"))
                Objeto.DescripcionFormal = CStr(dr("descripcionformal"))
                Objeto.DescripcioninFormal = CStr(dr("descripcioninformal"))
                Objeto.STOCKMINIMO = CStr(dr("stockminimo"))
                Objeto.RefFAmilia = CStr(dr("reffamilia"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerMaterialStock(ByVal rutProv As String) As ArrayList

        Dim query As String = "select  DISTINCT mat.id, mat.nombre from material mat, materialdatos md, ordencompra oc " + _
                            "where mat.id = md.fkmaterial and md.fkordencompra = oc.numorden and oc.fkproveedor ='" + rutProv + "'"



        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Material
            While dr.Read()
                Objeto = New Material()
                Objeto.Nombre = CStr(dr("nombre"))
                Objeto.ID = CStr(dr("id"))
            

                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerActividad(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Actividad
            While dr.Read()
                Objeto = New Actividad(CInt(dr("id")), CStr(dr("nombre")), _
                CStr(dr("descripcion")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerOrdenCompra(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As OrdenCompra
            While dr.Read()
                Objeto = New OrdenCompra(CStr(dr("numorden")), CStr(dr("fechadepedido")), _
                CStr(dr("fechadepago")))
                Objeto.rutProv = CStr(dr("fkproveedor"))
                Objeto.fkRequisicion = CStr(dr("fkrequisicion"))
                Objeto.Atencion = CStr(dr("atencion"))
                Objeto.condPago = CStr(dr("condpago"))
                Objeto.despachar = CStr(dr("despacho"))
                Objeto.Comprador = CStr(dr("comprador"))
                Objeto.notas = CStr(dr("notas"))

                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerLibro(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As LibroContable
            While dr.Read()
                Objeto = New LibroContable()
                Objeto.Iva = CStr(dr("iva"))
                Objeto.Neto = CStr(dr("neto"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerFacturaEmitida(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As FacturaEmitida
            While dr.Read()
                Objeto = New FacturaEmitida
                Objeto.CondVenta = CStr(dr("condventa"))
                Objeto.Fecha = CStr(dr("fecha"))
                Objeto.Iva = CStr(dr("iva"))
                Objeto.Neto = CStr(dr("neto"))
                Objeto.Total = CStr(dr("total"))
                Objeto.ID = CStr(dr("folio"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerFacturaRecibida(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As FacturaRecibida
            While dr.Read()
                Objeto = New FacturaRecibida
                Objeto.ID = CStr(dr("folio"))
                Objeto.CondVenta = CStr(dr("condventa"))
                Objeto.Fecha = CStr(dr("fecha"))
                Objeto.Iva = CStr(dr("iva"))
                Objeto.Neto = CStr(dr("neto"))
                Objeto.Total = CStr(dr("total"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerMail(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Mail
            While dr.Read()
                Objeto = New Mail(CStr(dr("smtphost")), CStr(dr("nombrecuenta")), CStr(dr("password")), CStr(dr("estado")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function ObtenerUsuario(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Usuario
            While dr.Read()
                Objeto = New Usuario(CStr(dr("usuario")), CStr(dr("password")))
                Objeto.RefEmpleado = CStr(dr("fkempleado"))
                Objeto.Grupo = CStr(dr("fkgrupousuarios"))
                Objeto.ID = CStr(dr("id"))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerMensajes(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Mensaje
            While dr.Read()
                Objeto = New Mensaje(CStr(dr("id")), CStr(dr("asunto")), CStr(dr("prioridad")), CStr(dr("cuerpo")), _
                        CStr(dr("fkusuario")), CStr(dr("remitente")), CStr(dr("fecha")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerSolicitudes(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As Solicitud
            While dr.Read()
                Objeto = New Solicitud(CStr(dr("id")), CStr(dr("descripcion")), CStr(dr("centrocosto")), CStr(dr("fkusuariosolicitante")), _
                        CStr(dr("fecha")), CStr(dr("fkmaterial")), CStr(dr("cantidad")), CStr(dr("estado")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerPresupuestoSubproyectoMaterial(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As presupuestosubproyectomaterial
            While dr.Read()
                Objeto = New presupuestosubproyectomaterial(CInt(dr("id")), CInt(dr("cantidad")), CStr(dr("descripcion")), CStr(dr("valorkg")), CInt(dr("fksubproyecto")), _
                        CInt(dr("fkmaterial")), CInt(dr("neto")), CInt(dr("iva")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Public Function obtenerPresupuestoSubproyectoActividad(ByVal query As String) As ArrayList
        Dim array As New ArrayList
        Dim dr As Npgsql.NpgsqlDataReader
        Try
            Me.Open()
            Dim mydataset As New DataSet
            Dim command As New NpgsqlCommand(query, conn)
            dr = command.ExecuteReader()
            Dim Objeto As presupuestosubproyectoactividad
            While dr.Read()
                Objeto = New presupuestosubproyectoactividad(CStr(dr("descripcion")), CInt(dr("id")), CInt(dr("cantidadhoras")), CInt(dr("valorhorahombre")), CInt(dr("factorhh")), CInt(dr("prioridadtarea")), _
                         CInt(dr("fksubproyecto")), CInt(dr("fkactividad")), CInt(dr("neto")), CInt(dr("iva")))
                array.Add(Objeto)
            End While
            Me.Close()
            Return array
        Catch ex As NpgsqlException
            MsgBox(ex.ToString)
        End Try
        Return Nothing
    End Function

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub


End Class

